﻿using app.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace eService.BLL
{
    public class ConnectionProvider
    {
        protected static AppSetting setting;
        public static DbContext DbEntites { get; set; }
        public static String ErrMsg { get; set; }
        public static IDbConnection ServerConnection { get; set; }
        public static EntityConnection EFCon { get; set; }
        public static String ConnString { get; set; }

        public static bool LoadAllConnection()
        {
            var KQ = false;
            InitAppSetting();
            KQ = LoadConnection();
            if (KQ)
            {
                getDbEntities();
            }
            return KQ;
        }

        public static void InitAppSetting()
        {
            setting = AppSetting.getSetting();
        }

        public static eServiceEntities getDbEntities()
        {
            if (DbEntites == null)
            {
                DbEntites = new eServiceEntities();
            }
            return (eServiceEntities)DbEntites;
        }

        public static bool LoadConnection()
        {
            string datasource, DBName, UserID, password;
            bool KQ = true;
            ErrMsg = "";

            datasource = AppSetting.getSetting().getConfigValue("ip_server");
            if (datasource == null)
            {
                return false;
            }

            DBName = AppSetting.getSetting().getConfigValue("database_server");
            if (DBName == null)
            {
                return false;
            }

            UserID = AppSetting.getSetting().getConfigValue("user_server");
            if (UserID == null)
            {
                return false;
            }

            password = AppSetting.getSetting().getConfigValue("password_server");
            if (password == null)
            {
                return false;
            }
            password = TCEncryption.ToDecrypt(password);
            string ConnectionString = GetSQLConnectionString(datasource, DBName, UserID, password);

            IDbConnection con = new EntityConnection(GetEFConnectionString(ConnectionString));
            IDbConnection con1 = new SqlConnection(ConnectionString);
            try
            {
                con.Open();
                ServerConnection = con1;
                EFCon = (EntityConnection)con;
                KQ = true;
                con.Close();
            }
            catch (Exception ex)
            {
                KQ = false;
                ErrMsg = "Không thể kết nối đến server dữ liệu. " + ex.Message;
            }
            finally
            {
                if (con.State != System.Data.ConnectionState.Closed)
                {
                    con.Close();
                }
            }
            return KQ;
        }
        public static bool LoadConnection(string Server, string DBName, string UserID, string password)
        {
            bool KQ = true;
            ErrMsg = "";
            string ConnectionString = GetSQLConnectionString(Server, DBName, UserID, password);

            IDbConnection con = new EntityConnection(GetEFConnectionString(ConnectionString));
            IDbConnection con1 = new SqlConnection(ConnectionString);
            try
            {
                con.Open();
                ServerConnection = con1;
                EFCon = (EntityConnection)con;
                KQ = true;
                con.Close();
            }
            catch (Exception ex)
            {
                KQ = false;
                ErrMsg = "Không thể kết nối đến server dữ liệu. " + ex.Message;
            }
            finally
            {
                if (con.State != System.Data.ConnectionState.Closed)
                {
                    con.Close();
                }
            }
            return KQ;
        }
        public static string GetSQLConnectionString(string Server, string DBName, string UserID, string Pass)
        {
            string ConnectionString = "";
            ConnectionString += "Data Source=" + Server + ";";
            if (DBName != null)
            {
                ConnectionString += "Initial Catalog=" + DBName + ";";
            }
            ConnectionString += "Persist Security Info=True;";
            ConnectionString += "User ID=" + UserID + ";";
            ConnectionString += "Password=" + Pass + ";";
            ConnectionString += "Connect Timeout=3;";

            SqlConnectionStringBuilder sqlbuilder = new SqlConnectionStringBuilder();
            sqlbuilder.ConnectionString = ConnectionString;
            sqlbuilder.ConnectTimeout = 3;
            ConnString = sqlbuilder.ToString();
            return sqlbuilder.ToString();
        }
        public static String GetEFConnectionString(String ConnectionString)
        {
            System.Data.EntityClient.EntityConnectionStringBuilder builder = new System.Data.EntityClient.EntityConnectionStringBuilder();
            builder.Provider = "System.Data.SqlClient";
            builder.ProviderConnectionString = ConnectionString;
            builder.Metadata = "res://*";

            return builder.ToString();
        }
        public static string GetEFConnectionString(string Server, string DBName, string UserID, string Pass)
        {
            string ConnectionString = GetSQLConnectionString(Server, DBName, UserID, Pass);
            return GetEFConnectionString(ConnectionString);
        }

        public static List<string> LoadList_Database(string server, string user_id, string passw, bool ISecurity)
        {
            List<string> listDB = new List<string>();

            SqlConnection Conn = new SqlConnection("Data Source=" + server + ";Integrated Security=" + ISecurity + "; User id=" + user_id + "; Password=" + passw + "");

            Conn.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = Conn;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "sp_databases";

            SqlDataReader DReader = command.ExecuteReader();

            while (DReader.Read())
            {
                listDB.Add(DReader.GetString(0));
            }
            return listDB;
        }
    }
}
